package club.jdiy.utils.poi;

import club.jdiy.utils.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.Date;
import java.util.stream.IntStream;

/**
 * JDiy实现的一个Excel工具类，可以很方便的将POJO对象导出到Excel.
 *
 * <p/>示例代码参考：
 * <pre>
 *     ExcelUtils et = new ExcelUtils(300);//参数300可以省略（通常设置为分页导出时的pageSize值,以获得更好的导出性能）
 *
 *     //添加Excel工作薄(名称为：sheet 1),并设置该工薄的数据表头：
 *     et.addSheet("sheet 1", new MetaSet[]{
 *         new MetaSet("姓名", "name"),   //列：表头和内容的导出映射关系 详见{@link MetaSet}类的各构造函数的用法.
 *         new MetaSet("年龄", "age"),
 *         new MetaSet("住址", 200, "addr"), //200:自定义该列显示的宽度
 *         new MetaSet("班主任姓名", "teacher.name"),  //子成员变量
 *         new MetaSet("班主任任教学科", "teacher.xueke.name"), //任意多层引用
 *         new MetaSet("是否欠费", "qianFei"), //boolean字段导出为：是|否
 *         new MetaSet("注册时间", "dtAdd"), //LocalDate自动格式化为：yyyy-MM-dd, LocalDateTime自动格式化为：yyyy-MM-dd HH:mm:ss
 *         new MetaSet("性别", new Formatter<User>() {
 *             public Object fmt(User user) {
 *                 return user.getGender()==1 ? "男":"女";
 *             }
 *         }), //自定义的格式化输出（常规写法）
 *         new MetaSet("性别2", (Formatter&lt;User&gt;) o -> gender==1?"男":"女" )   //自定义的格式化输出(lambla表达式写法)
 *     });
 *
 *     //可以在此处添加工作薄内表头或表脚的标题文字（会自动合并单元格）
 *     //et.addHeader("某某学校X班学生信息统计表");
 *     //et.addHeader("本表统计于：x年x月x日");
 *
 *     //添加要导出的数据，可以多次调用. （若数据量大，建议采用使用分页查询，循环导出全部页码的数据，不要一次性查出所有数据，以免因数据太多导致内存溢出）。
 *     et.addData(pager.getItems());
 *
 *     //此处还可以继续添加工作薄，往另一个工作薄导数据：
 *     // et.addSheet("sheet 2", ..);
 *     // et.addData(..);
 *
 *     et.output(request, response, "查询结果导出.xlsx"); //导出下载，并指定显示的文件名(扩展名可以是xls或者xlsx, 若省略默认为xls)
 *     et.output(new File("d:/test.xlsx")); //导出到文件
 * </pre>
 * <p>
 *
 * @author ziquee(子秋) QQ号:39886616 主页: http://www.jdiy.club
 * @version 20201016
 */
@SuppressWarnings("ALL")
public class ExcelUtils implements Export {
    private Workbook workbook;
    private Sheet sheet;
    private MetaSet[] meta;
    private CellStyle headStyle;//标题行样式
    private CellStyle bodyStyle;//数据行样式
    private int index = 0;//行号。
    private boolean isWriteMeta = false;//记录Meta元数据是否已经写入sheet.

    public ExcelUtils() {
        this(0);
    }

    public ExcelUtils(int rowAccessWindowSize) {
        if (rowAccessWindowSize > 0) {
            workbook = new SXSSFWorkbook(rowAccessWindowSize);
        } else {
            workbook = new HSSFWorkbook();
        }

        //-----headStyle
        headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);
        headStyle.setAlignment(HorizontalAlignment.CENTER);

        Font font = workbook.createFont();
        font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        headStyle.setFont(font);


        //-----bodyStyle
        bodyStyle = workbook.createCellStyle();
        bodyStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        bodyStyle.setBorderBottom(BorderStyle.THIN);
        bodyStyle.setBorderLeft(BorderStyle.THIN);
        bodyStyle.setBorderRight(BorderStyle.THIN);
        bodyStyle.setBorderTop(BorderStyle.THIN);
        bodyStyle.setAlignment(HorizontalAlignment.CENTER);
        bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font font2 = workbook.createFont();
        font2.setBold(false);
        bodyStyle.setFont(font2);
    }

    /**
     * 创建sheet工作薄.
     *
     * @param sheetName sheet名称
     * @param meta      sheet 列头定义设置.
     */
    public ExcelUtils addSheet(String sheetName, MetaSet[] meta) {
        sheet = workbook.createSheet(sheetName);
        sheet.setDefaultColumnWidth(15);
        this.meta = meta;
        return this;
    }

    /**
     * 为当前的Sheet附加数据.
     * 调用此方法之前，必须先调用{@link #addSheet(String, MetaSet[])}方法，完成MetSet的设置.
     *
     * @param dataset 数据集合
     */
    public void addData(Collection dataset) {
        if (meta == null || meta.length < 1) throw new RuntimeException("尚未创建 Sheet.");
        writeMetaSet();
        if (dataset == null) return;

        //遍历集合数据，产生数据行
        for (Object o : dataset) {
            Row row = sheet.createRow(index++);
            Object t = o;
            int j = 0;
            for (MetaSet ms : meta) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(bodyStyle);
                @SuppressWarnings("unchecked")
                Object value = ms.getFormatter().fmt(t);
                if (value == null) cell.setCellValue("");
                else if (value instanceof Boolean) {
                    cell.setCellValue((Boolean) value ? "是" : "否");
                } else if (value instanceof LocalDate) {
                    cell.setCellValue(new HSSFRichTextString(DateUtils.fmt_date.format((LocalDate) value)));
                } else if (value instanceof LocalDateTime) {
                    cell.setCellValue(new HSSFRichTextString(DateUtils.fmt_datetime.format((LocalDateTime) value)));
                } else if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cell.setCellValue(new HSSFRichTextString(sdf.format(date)));
                } else if (value instanceof Number) {
                    Number v = (Number) value;
                    if (v.doubleValue() == v.intValue()) cell.setCellValue(v.intValue());
                    else cell.setCellValue(v.doubleValue());
                } else {
                    //其它数据类型都当作字符串简单处理
                    String textValue = value.toString();
                    /*Pattern p = Pattern.compile("^[1-9]\\d*(\\.\\d+)?$");//是数字当作double处理(但以0开头的，如005，应当字符串处理)
                    if ("0".equals(textValue) || p.matcher(textValue).matches()) {
                        cell.setCellValue(Double.parseDouble(textValue));
                    } else {*/
                    HSSFRichTextString richString = new HSSFRichTextString(textValue);
                    cell.setCellValue(richString);
                    //}
                }
                j++;
            }
        }
    }

    /**
     * 添加默认文字表头(单行合并居中，默认16号字粗体,行高27).
     *
     * @param text 文字内容
     */
    public void addHeader(String text) {
        this.addHeader(text, 0,
                null, null,
                (short) 16, true, 30, 2, 2);
    }

    /**
     * 添加默认文字表脚(单元格合并居右，默认字体，行高25)
     *
     * @param text 文字内容
     */
    public void addFooter(String text) {
        this.addHeader(text, index++,
                null, null,
                null, false, 25, 3, 2);
    }

    /**
     * 添加自定义文字表头.  可多次调用此方法，来设置多个表头文字.
     *
     * @param text      文字内容
     * @param rowNum    文字表头添加在第几行. 默认为0代表第一行.
     * @param cellBegin 文字表头(合并)起始列. 默认0(第一列)
     * @param cellEnd   文字表头(合并)结束列. 默认为meta.length(最后一列)
     * @param fontSize  字体大小.
     * @param fontBold  字体是否加粗
     * @param height    行高
     * @param align     水平对齐:   1:左  2:中  3:右
     * @param valign    垂直对齐:   1:顶  2:中  3:底
     */
    public void addHeader(String text, int rowNum,
                          Integer cellBegin, Integer cellEnd,
                          Short fontSize, boolean fontBold, Integer height, int align, int valign) {
        index = Math.max(index, rowNum + 1);
        cellBegin = cellBegin == null ? 0 : cellBegin;
        Row row = sheet.getRow(rowNum);
        if (row == null) row = sheet.createRow(rowNum);

        CellStyle style = workbook.createCellStyle();
        if (fontSize != null || fontBold) {
            Font font = workbook.createFont();
            if (fontSize != null) font.setFontHeightInPoints(fontSize);
            if (fontBold) font.setBold(true);
            style.setFont(font);
        }

        if (height != null) row.setHeightInPoints(height);

        if (align == 1) style.setAlignment(HorizontalAlignment.LEFT);
        else if (align == 2) style.setAlignment(HorizontalAlignment.CENTER);
        else if (align == 3) style.setAlignment(HorizontalAlignment.RIGHT);

        if (valign == 1) style.setVerticalAlignment(VerticalAlignment.TOP);
        else if (valign == 2) style.setVerticalAlignment(VerticalAlignment.CENTER);
        else if (valign == 3) style.setVerticalAlignment(VerticalAlignment.BOTTOM);

        Cell cell = row.createCell(cellBegin);
        cell.setCellStyle(style);

        HSSFRichTextString richString = new HSSFRichTextString(text);
        cell.setCellValue(richString);
        if (cellEnd == null || cellEnd > cellBegin) { //注意：cellEnd不为null，并且值小于等于cellBegin时，不合并单元格
            CellRangeAddress region = new CellRangeAddress(
                    rowNum, rowNum,
                    cellBegin, cellEnd == null ? meta.length - 1 : cellEnd
            );
            sheet.addMergedRegion(region);
        }
    }

    public int getRowCount() {
        return index;
    }

    /**
     * Response 根据给定的名称输出下载
     *
     * @param response HttpServletResponse对象
     * @param fileName 在客户端要显示的名称(兼容IE)
     * @throws IOException
     */
    public void output(HttpServletRequest request, HttpServletResponse response, String fileName) throws IOException {

        writeMetaSet();
        if (fileName == null) fileName = "导出结果.xls";
        if (!fileName.endsWith(".xls") && !fileName.endsWith(".xlsx")) fileName += ".xls";
        if (fileName.endsWith(".xlsx"))
            response.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        else
            response.setHeader("Content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment; filename="
                + getFileName(request,fileName));
        output(response.getOutputStream());
    }

    /**
     * 输出到指定的流
     *
     * @param out OutputStream对象
     * @throws IOException
     */
    public void output(OutputStream out) throws IOException {
        writeMetaSet();
        workbook.write(out);
    }

    /**
     * 输出到文件
     *
     * @param file file对象
     * @throws IOException
     */
    public void output(File file) throws IOException {
        writeMetaSet();
        workbook.write(new FileOutputStream(file));
    }


    private String getFileName(HttpServletRequest request, String name) throws UnsupportedEncodingException {
        String userAgent = request.getHeader("USER-AGENT");
        return userAgent.contains("Mozilla")
                ? new String(name.getBytes(), "ISO8859-1")
                : new String(name.getBytes("gb2312"), "iso8859-1");
    }

    private void writeMetaSet() {
        if (isWriteMeta) return;
        isWriteMeta = true;
        Row row = sheet.createRow(index++);
        row.setHeightInPoints(18);
        IntStream.range(0, meta.length).forEach(i -> {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            HSSFRichTextString text = new HSSFRichTextString(meta[i].getHead());
            cell.setCellValue(text);
            sheet.setColumnWidth(i, (int) (meta[i].getWidth() * 35.7));
        });
    }

    @SuppressWarnings("ALL")
    private Object revokeValue(Object o, String fieldName) {
        String uField = fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
        String getName1 = "get" + uField;
        Class tCls = o.getClass();
        Method getMethod;
        try {
            getMethod = tCls.getMethod(getName1, new Class[]{});
        } catch (NoSuchMethodException ignore) {
            String getName2 = "is" + uField;
            try {
                getMethod = tCls.getMethod(getName2, new Class[]{});
            } catch (NoSuchMethodException nsme) {
                throw new NoSuchMethodError("没有找到字段" + fieldName + "的访问器('" +
                        getName1 + "'或者'" + getName2 + "')");
            }
        }
        try {
            return getMethod.invoke(o);
        } catch (Exception e) {
            throw new NoSuchMethodError("错误的字段访问器'" + getMethod.getName() + "'");
        }
    }
}
